-- @owner: lihongji
-- @date: 2022-07-19
-- @testpoint: var_samp-over-distinct的测试，合理报错

--step1:建表；expect：成功
drop table if exists t_ustore_var_samp_case0037;
create table  t_ustore_var_samp_case0037(
     col_1 integer,
     col_2 bigint,
     col_3 float8,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 smalldatetime,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);
--step2：创建序列；expect：成功
drop sequence if exists s_ustore_var_samp_case0037;
create sequence s_ustore_var_samp_case0037 increment by 1 start with 10;
--step3：清空数据；expect：成功
truncate table t_ustore_var_samp_case0037;
--step4：插入数据；expect：成功
begin
	for i in 1..20 loop
      insert into t_ustore_var_samp_case0037 values(
	  i,
      s_ustore_var_samp_case0037.nextval,
	  i+445.255,
	  98*0.99*i,
	  true,
	  lpad('abc','30','@'),
	  null,
	  rpad('abc','20','e'),
	  (interval '4 5:12:10.222' day to second(3)),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  lpad('10','12','01010')::blob,
	  rpad('abc','9','a@123&^%djgk'),
	  '{32,535,5645645,6767,76,67,56,48,979,978,7}'
	  );
    end loop;
end;
/
--step5：var_samp-over-distinct的测试；expect：失败
select var_samp(distinct col_2) over(partition by col_4 order by col_1) from t_ustore_var_samp_case0037 order by 1;
select var_samp(distinct col_3) over(partition by col_5 order by col_3) from t_ustore_var_samp_case0037 order by 1;
select var_samp(distinct abs(col_1)+ceil(col_3)*floor(col_4)) over(partition by col_6 order by col_5) from t_ustore_var_samp_case0037 order by 1;
select distinct var_samp(distinct col_2) over(partition by col_7 order by col_8) from t_ustore_var_samp_case0037 order by 1;
--step6：清理环境；expect：成功
drop table if exists t_ustore_var_samp_case0037;
drop sequence if exists s_ustore_var_samp_case0037;
